CREATE PROCEDURE [dbo].[asi_CreateRFMStatsManual]

@rfmKey uniqueidentifier

AS

declare @now datetime

set @now = getdate()

-- new code
select
ContactKey,
count(*) as CT,
sum (Amount) as AMT,
min (Amount) as LOW,
max (Amount) as HIGH,
min ([TransactionDate]) as FIRST,
max ([TransactionDate]) as LAST,
convert (datetime, '1/1/90') as HIGHCONT,
10000000.0000 as FIRSTAMT,
10000000.0000 as LASTAMT
into #tmpTransWork
from #tmpTrans
group by ContactKey

update #tmpTransWork
set HIGHCONT = #tmpTrans.TransactionDate from #tmpTrans
where #tmpTransWork.ContactKey = #tmpTrans.ContactKey
and #tmpTransWork.HIGH = #tmpTrans.Amount

update #tmpTransWork
set FIRSTAMT = #tmpTrans.Amount from #tmpTrans
where #tmpTransWork.ContactKey = #tmpTrans.ContactKey
and #tmpTransWork.FIRST = #tmpTrans.TransactionDate

update #tmpTransWork
set LASTAMT = #tmpTrans.Amount from #tmpTrans
where #tmpTransWork.ContactKey = #tmpTrans.ContactKey
and #tmpTransWork.LAST = #tmpTrans.TransactionDate

-- existing code, updated statement
-- Update the RFMMain Table, set initial values for all columns
UPDATE RFMMain
SET NumContacts = T1.CT,
TotalAmount = T1.AMT,
LowAmount = T1.LOW,
HighAmount = T1.HIGH,
FirstContact = T1.FIRST,
LastContact = T1.LAST,
HighContact = T1.HIGHCONT,
FirstContactAmount = T1.FIRSTAMT,
LastContactAmount = T1.LASTAMT,
NumDays = DATEDIFF(DAY, T1.LAST, @now),
Frequency = 0,
Recency = 0,
Monetary = 0,
Combined = 0,
Total = 0
FROM
(SELECT
ContactKey,
CT,
AMT,
LOW,
HIGH,
FIRST,
LAST,
HIGHCONT,
FIRSTAMT,
LASTAMT
FROM #tmpTransWork) AS T1
WHERE RFMMain.RFMKey = @rfmKey AND RFMMain.ContactKey = T1.ContactKey

-- Update the Recency Rankings
update RFMMain
    set Recency = S1.Ranking
    from (select RFMMain.ContactKey, #tmpRanking.Ranking from RFMMain, #tmpRanking
    where RFMMain.NumDays is NOT NULL and RFMMain.NumDays between
    #tmpRanking.RecencyStart and #tmpRanking.RecencyEnd) AS S1
    where RFMMain.RFMKey = @rfmKey and RFMMain.ContactKey = S1.ContactKey
    
-- Update the Frequency Rankings
update RFMMain
    set Frequency = S1.Ranking
    from (select RFMMain.ContactKey, #tmpRanking.Ranking from RFMMain, #tmpRanking
    where RFMMain.NumContacts is NOT NULL and NumContacts != 0 and RFMMain.NumContacts between
    #tmpRanking.FrequencyStart and #tmpRanking.FrequencyEnd) AS S1
    where RFMMain.RFMKey = @rfmKey and RFMMain.ContactKey = S1.ContactKey

-- Update the Monetary Rankings
update RFMMain
    set Monetary = S1.Ranking
    from (select RFMMain.ContactKey, #tmpRanking.Ranking from RFMMain, #tmpRanking
    where RFMMain.TotalAmount is NOT NULL and TotalAmount != 0 and RFMMain.TotalAmount between
    #tmpRanking.MonetaryStart and #tmpRanking.MonetaryEnd) AS S1
    where RFMMain.RFMKey = @rfmKey and RFMMain.ContactKey = S1.ContactKey

-- Set the combined and total rankings    
update RFMMain
    set Combined = Recency*10000+Frequency*100+Monetary,
    Total = Recency+Frequency+Monetary
    where RFMMain.RFMKey = @rfmKey

drop table #tmpTrans    
drop table #tmpRanking

GO